/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package connections;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import main.ApplicatieMain;
//gtcn_ktv
//        ktv06

/**
 *
 * @author Jeroen
 */
public class Query {

    private Database database = null;
    private Connection conn = null;
    private ApplicatieMain applicatieMain;

    public Query() {
        applicatieMain = ApplicatieMain.getInstance();
        database = new Database();
        conn = database.connect();
    }

    //Get's voor de UserData tabel
    public int getUserID(String username) {
        int userID = 0;
        try {
            String sql = "SELECT userID FROM userdata WHERE naam='" + username + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userID = result.getInt("userID");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userID;
    }

    public String getPassword(int userID) {
        String password = "";
        try {
            String sql = "SELECT password FROM userdata WHERE userID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                password = result.getString("password");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return password;
    }

    public String getUsername(int userID) {
        String username = "";
        try {
            String sql = "SELECT naam FROM userdata WHERE userID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                username = result.getString("username");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return username;
    }

    public int getUserPrivelege(int userID) {
        int userPrivelege = 0;
        try {
            String sql = "SELECT UserPrivelege FROM userdata WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userPrivelege = result.getInt("UserPrivelege");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userPrivelege;
    }

    //Get's voor de UserLog tabel
    public void addUserLog(int userID) {
        String sql = "INSERT INTO userlog (userID) VALUES('" + userID + "')";
        database.insert(conn, sql);
    }

    public int getUserDifficulty(int userID) {
        int userDifficulty = 0;
        try {
            String sql = "SELECT UserDifficulty FROM userlog WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userDifficulty = result.getInt("UserDifficulty");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userDifficulty;
    }

    public void setUserDifficulty(int userDifficulty, int userID) {
        String sql = "UPDATE userlog SET UserDifficulty='" + userDifficulty + "' WHERE UserID='" + userID + "'";
        database.executeUpdate(conn, sql);
    }

    public int getUserScore(int userID) {
        int userScore = 0;
        try {
            String sql = "SELECT UserScore FROM userlog WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userScore = result.getInt("UserScore");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userScore;
    }

    public void setUserScore(int userScore, int userID) {
        String sql = "UPDATE userlog SET UserScore='" + userScore + "' WHERE UserID='" + userID + "'";
        database.executeUpdate(conn, sql);
    }

    public String getUserAchievements(int userID) {
        String userAchievements = "";
        try {
            String sql = "SELECT UserAchievements FROM userlog WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userAchievements = result.getString("UserAchievements");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userAchievements;
    }

    public int getUserWrong(int userID) {
        int userWrong = 0;
        try {
            String sql = "SELECT GuessedWrong FROM userlog WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userWrong = result.getInt("GuessedWrong");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userWrong;
    }

    public int getUserCorrect(int userID) {
        int userCorrect = 0;
        try {
            String sql = "SELECT GuessedCorrect FROM userlog WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userCorrect = result.getInt("GamesWon");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userCorrect;
    }

    public void setUserCorrect(int correct, int userID){
        String sql = "UPDATE userlog SET GuessedCorrect='" + correct + "' WHERE UserID='" + userID + "'";
        database.executeUpdate(conn, sql);
    }

    public void setUserLost(int lost, int userID){
        String sql = "UPDATE userlog SET GamesLost='" + lost + "' WHERE UserID='" + userID + "'";
        database.executeUpdate(conn, sql);
    }

    public int getUserLost(int userID) {
        int userLost = 0;
        try {
            String sql = "SELECT GamesLost FROM userlog WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userLost = result.getInt("GamesLost");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userLost;
    }

    public void setUserWon(int won, int userID){
        String sql = "UPDATE userlog SET GamesWon='" + won + "' WHERE UserID='" + userID + "'";
        database.executeUpdate(conn, sql);
    }

    public int getUserWon(int userID) {
        int userWon = 0;
        try {
            String sql = "SELECT GamesWon FROM userlog WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userWon = result.getInt("GamesWon");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userWon;
    }


    public void setUserWonMulti(int won, int userID){
        String sql = "UPDATE userlog SET GamesWonMulti='" + won + "' WHERE UserID='" + userID + "'";
        database.executeUpdate(conn, sql);
    }

    public int getUserWonMulti(int userID) {
        int userWonMulti = 0;
        try {
            String sql = "SELECT GamesWonMulti FROM userlog WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userWonMulti = result.getInt("GamesWonMulti");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userWonMulti;
    }

    public void setUserLostMulti(int lost, int userID){
        String sql = "UPDATE userlog SET GamesLostMulti='" + lost + "' WHERE UserID='" + userID + "'";
        database.executeUpdate(conn, sql);
    }

    public int getUserLostMulti(int userID) {
        int userLostMulti = 0;
        try {
            String sql = "SELECT GamesLostMulti FROM userlog WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userLostMulti = result.getInt("GamesLostMulti");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userLostMulti;
    }

    public int getUserUnfinished(int userID) {
        int userUnfinished = 0;
        try {
            String sql = "SELECT GamesUnfinished FROM userlog WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userUnfinished = result.getInt("GamesUnfinished");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userUnfinished;
    }

    public int getUserFaultless(int userID) {
        int userFaultless = 0;
        try {
            String sql = "SELECT TotalFaultless FROM userlog WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userFaultless = result.getInt("TotalFaultless");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userFaultless;
    }

    public int getUserSessions(int userID) {
        int userSessions = 0;
        try {
            String sql = "SELECT TotalSessions FROM userlog WHERE UserID='" + userID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                userSessions = result.getInt("TotalSessions");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userSessions;
    }

    //Get's voor de Achievement Tabel
    public int getAchievementID(int achievName) {
        int achievID = 0;
        try {
            String sql = "SELECT AchievementID FROM achievement WHERE AchievementName='" + achievName + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                achievID = result.getInt("AchievementName");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return achievID;
    }

    public String getAchievementName(int achievID) {
        String achievName = "";
        try {
            String sql = "SELECT AchievementName FROM achievement WHERE AchievementID='" + achievID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                achievName = result.getString("AchievementName");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return achievName;
    }

    public String getAchievementRequirement(int achievID) {
        String achievReq = "";
        try {
            String sql = "SELECT AchievementRequirement FROM userlog WHERE AchievementID='" + achievID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                achievReq = result.getString("AchievementRequirement");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return achievReq;
    }

    public void setUserKlas(String klas, String leerlingNaam){
        String sql = "UPDATE userdata SET klas ='" + klas
                + "' WHERE naam='" + leerlingNaam
                + "' AND brinNummer='" + applicatieMain.getBrinNummer() + "'";
        database.executeUpdate(conn, sql);
    }

    public void userChangeName(String oldname, String newname){
        String sql = "UPDATE userdata SET naam ='" + newname
                + "' WHERE naam='" + oldname
                + "' AND brinNummer='" + applicatieMain.getBrinNummer() + "'";
        database.executeUpdate(conn, sql);
    }

    //Get's voor de Wordlist tabel
    public void addWord(String word, int difficulty,
            String explanation, int woordenlijstID) {

        String sql = "INSERT INTO woord "
                + "(Word, WordDifficulty, WordExplanation, woordenlijstID)"
                + "VALUES('"
                + word + "', '"
                + difficulty + "', '"
                + explanation + "', '"
                + woordenlijstID + "')";
        database.executeUpdate(conn, sql);
    }

    public String getRandomWord(int userLevel, int woordenlijstID) {
        String word = null; // Willekeurig woord
        try {
            String query = "SELECT COUNT(*) AS cnt FROM woord WHERE WordDifficulty='" + userLevel + "'" + "AND woordenlijstID='" + woordenlijstID + "'"; // Query for aantal rows
            ResultSet result = database.execute(conn, query);
            result.next();
            int rows = result.getInt("cnt");
            if (rows > 0) {
                Random rand = new Random();
                int randNumber = rand.nextInt(rows);

                query = "SELECT Word FROM woord WHERE WordDifficulty='" + userLevel + "'" + "AND woordenlijstID='" + woordenlijstID + "' LIMIT " + randNumber + " , 1"; // 1 rij ophalen en beginnen bij randNumber
                result = database.execute(conn, query);
                result.next();
                word = result.getString("Word");
            } else {
                applicatieMain.showPage(new view.SpelerOptiesPagina());
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return word;
    }

    public int getWordID(String word, int woordenlijstID) {
        int wordID = -1;
        try {
            String sql = "SELECT WordID FROM woord WHERE woordenlijstID='" + woordenlijstID + "'" + " AND Word='" + word + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                wordID = result.getInt("WordID");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return wordID;
    }

    public String getWord(int wordID, int woordenlijstID) {
        String word = null;
        try {
            String sql = "SELECT Word FROM woord WHERE WordID='" + wordID + "'" + "AND woordenlijstID='" + woordenlijstID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                word = result.getString("Word");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return word;
    }

    public int getWordDifficulty(int wordID, int woordenlijstID) {
        int wordDifficulty = 0;
        try {
            String sql = "SELECT WordDifficulty FROM woord WHERE WordID='" + wordID + "'" + "AND woordenlijstID='" + woordenlijstID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                wordDifficulty = result.getInt("WordDifficulty");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return wordDifficulty;
    }

    public String getWordLanguage(int wordID, int woordenlijstID) {
        String wordLanguage = "";
        try {
            String sql = "SELECT WordLanguage FROM woord WHERE WordID='" + wordID + "'" + "AND woordenlijstID='" + woordenlijstID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                wordLanguage = result.getString("WordLanguage");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return wordLanguage;
    }

    public String getWordExplanation(int wordID, int woordenlijstID) {
        String wordExplanation = "";
        try {
            String sql = "SELECT WordExplanation FROM woord WHERE WordID='" + wordID + "'" + "AND woordenlijstID='" + woordenlijstID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                wordExplanation = result.getString("WordExplanation");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return wordExplanation;
    }

    public void deleteWord(int wordID) {

        String sql = "DELETE FROM woord WHERE WordID='" + wordID + "'";
        database.executeUpdate(conn, sql);
    }

    // word list tabel
    public void addWordList(int klasID, String lijstNaam) {

        String sql = "INSERT INTO woordenlijst "
                + "(klasID, lijstNaam, standaard)"
                + "VALUES('"
                + klasID + "', '"
                + lijstNaam + "', '"
                + "0')"; // Normale woordenlijst
        database.executeUpdate(conn, sql);
    }

    public int getWordListID(int klasID, String lijstNaam) {
        int woordenlijstID = -1;
        try {
            String sql = "SELECT lijstID FROM woordenlijst WHERE (klasID='" + klasID + "' AND lijstNaam='" + lijstNaam + "') OR (standaard='1')";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                woordenlijstID = result.getInt("lijstID");
            }

        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return woordenlijstID;
    }
    
    public int wordListExists(int klasID, String lijstNaam) {
        int woordenlijstID = -1;
        try {
            String sql = "SELECT lijstID FROM woordenlijst WHERE (klasID='" + klasID + "' AND lijstNaam='" + lijstNaam + "')";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                woordenlijstID = result.getInt("lijstID");
            }

        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return woordenlijstID;
    }
        
    
    public void deleteWordList(int woordenlijstID) {
        String sql = "DELETE FROM woordenlijst WHERE lijstID='" + woordenlijstID + "'";
        database.executeUpdate(conn, sql);

        sql = "DELETE FROM woord WHERE woordenlijstID='" + woordenlijstID + "'";
        database.executeUpdate(conn, sql);
    }

    public void addWordsToTable(JTable table, int woordenlijstID) {
        try {
            String sql = "SELECT WordID, Word, WordDifficulty, WordExplanation FROM woord WHERE woordenlijstID='" + woordenlijstID + "'";
            ResultSet result = database.execute(conn, sql);
            DefaultTableModel model = (DefaultTableModel) table.getModel();
            while (result.next()) {
                model.addRow(new Object[]{
                            result.getString("WordID"),
                            result.getString("Word"),
                            result.getString("WordDifficulty"),
                            result.getString("WordExplanation"),});
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
    }

    public void updateWordsByTable(JTable table) {
        for (int i = 0; i < table.getRowCount(); i++) {
            String sql = "UPDATE woord SET Word='" + table.getValueAt(i, 1) + "', WordDifficulty='"
                    + table.getValueAt(i, 2) + "', WordExplanation='" + table.getValueAt(i, 3)
                    + "' WHERE WordID='" + table.getValueAt(i, 0) + "'";
            database.executeUpdate(conn, sql);
        }
    }
    public boolean isDefaultWordList(int woordenlijstID){
        System.out.println("TESTER:" + woordenlijstID );
        try {
            String sql = "SELECT standaard FROM woordenlijst WHERE lijstID='" + woordenlijstID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                return result.getBoolean("standaard");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return true; // Doesn't exist......
    }

    public void addWordListsToTable(JTable table, int klasID) {
        try {
            String sql = "SELECT lijstID, lijstNaam FROM woordenlijst WHERE (klasID='" + klasID + "') OR (standaard='1')";
            ResultSet result = database.execute(conn, sql);
            DefaultTableModel model = (DefaultTableModel) table.getModel();
            while (result.next()) {
                model.addRow(new Object[]{
                            result.getString("lijstID"),
                            result.getString("lijstNaam"),});
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
    }

    public List<String> getWordLists(int klasID) {
        List<String> wordList = new ArrayList<String>();

        try {
            String sql = "SELECT lijstNaam FROM woordenlijst WHERE (klasID='" + klasID + "') OR (standaard='1')";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                wordList.add(result.getString("lijstNaam"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }

        return wordList;
    }

    public void updateWordListByTable(JTable table) {
        for (int i = 0; i < table.getRowCount(); i++) {
            String sql = "UPDATE woordenlijst SET lijstNaam='" + table.getValueAt(i, 1)
                    + "' WHERE lijstID='" + table.getValueAt(i, 0) + "'";
            database.executeUpdate(conn, sql);
        }
    }

    /***
     * @param brinNummer The brinNummer entered in the brinNummmer field
     * @param inputWachtwoord The password entered in the password field
     * @return true if the user entered the correct password
     */
    public boolean checkPassword(String brinNummer, String inputWachtwoord) {
        boolean goedWachtwoord = false;

        try {
            String sql = "SELECT password FROM school WHERE "
                    + "brinNummer=? AND password=SHA(?)";

            //Gebruik PreparedStatement om SQL injections te voorkomen
            PreparedStatement prepStm = conn.prepareStatement(sql);
            prepStm.setString(1, brinNummer);
            prepStm.setString(2, inputWachtwoord);

            ResultSet result = prepStm.executeQuery();

            if (result.next()) {
                goedWachtwoord = true;
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return goedWachtwoord;

    }

    public boolean checkLeraarPassword(String inputWachtwoord) {
        boolean goedWachtwoord = false;

        try {
            String sql = "SELECT password FROM leraar WHERE "
                    + "brinNummer='" + applicatieMain.getBrinNummer()
                    + "' AND password=SHA(?)";

            PreparedStatement prepStm = conn.prepareStatement(sql);
            prepStm.setString(1, inputWachtwoord);
            ResultSet result = prepStm.executeQuery();

            if (result.next()) {
                goedWachtwoord = true;
            }

        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return goedWachtwoord;

    }

    public List<String> getKlassenLijst(int brinNummer) {
        List<String> klassenLijst = new ArrayList<String>();
        String sql = "SELECT klasNaam FROM klas WHERE brinNummer='" + brinNummer + "' ORDER BY klasNaam ASC";
        ResultSet result = database.execute(conn, sql);
        try {
            while (result.next()) {
                klassenLijst.add(result.getString("klasNaam"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }

        return klassenLijst;
    }

    public List<String> getLeerlingLijst(int brinNummer, String klas) {
        List<String> leerlingLijst = new ArrayList<String>();
        String sql = "SELECT naam "
                + "FROM userdata "
                + "WHERE brinNummer='" + brinNummer + "' AND klas='" + klas + "' "
                + "ORDER BY naam ASC";
        ResultSet result = database.execute(conn, sql);
        try {
            while (result.next()) {
                leerlingLijst.add(result.getString("naam"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }

        return leerlingLijst;
    }

    public void addKlas(String nieuweKlasNaam) {
        String sql = "INSERT INTO klas (brinNummer,"
                + "klasNaam) VALUES('"
                + ApplicatieMain.getInstance().getBrinNummer() + "'"
                + ",'" + nieuweKlasNaam + "')";
        database.executeUpdate(conn, sql);
    }

    public String getKlasName(int klasID) {

        String klasName = null;
        try {
            String sql = "SELECT klasNaam FROM klas WHERE klasID='" + klasID + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                klasName = result.getString("klasNaam");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return klasName;
    }

    public int getKlasID(String klasNaam) {

        int klasID = -1;
        try {
            String sql = "SELECT klasID FROM klas WHERE klasNaam='" + klasNaam + "'" + " AND brinNummer='" + ApplicatieMain.getInstance().getBrinNummer() + "'";
            ResultSet result = database.execute(conn, sql);
            while (result.next()) {
                klasID = result.getInt("klasID");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return klasID;
    }

    public void addLeerling(String klasNaam, String nieuweLeerlingNaam) {
        String sql = "INSERT INTO userdata (brinNummer,"
                + "naam, klas, status) VALUES('"
                + applicatieMain.getBrinNummer() + "'"
                + ",'" + nieuweLeerlingNaam + "','" + klasNaam + "',0)";
        database.executeUpdate(conn, sql);
        sql = "INSERT INTO userlog (userID) VALUES('" + getUserID(nieuweLeerlingNaam) + "')";
        database.executeUpdate(conn, sql);
    }

    public int getLeerlingID(String klasNaam, String leerlingNaam){
        int userID = -1;
        try {
            String sql = "SELECT userID FROM userdata WHERE naam='" + leerlingNaam + "'"
                    + " AND brinNummer='" + applicatieMain.getBrinNummer() + "'"
                    + " AND klas='" + klasNaam + "'";
            ResultSet result =  database.execute(conn, sql);
            while (result.next()) {
                userID = result.getInt("userID");
            }
        } catch (SQLException e) {
            System.out.println(Database.SQL_EXCEPTION + e.getMessage());
        }
        return userID;
    }

    public void deleteKlas(String klasNaam, int brinNummer) {
        String sql = "DELETE FROM klas WHERE klasNaam='"+klasNaam+"' AND brinNummer='" +brinNummer+ "'";
        database.executeUpdate(conn, sql);
        sql = "DELETE FROM userdata WHERE klas='" +klasNaam+ "' AND brinNummer='" +brinNummer+ "'";
        database.executeUpdate(conn, sql);
     
        
    }

    public void klasChangeName(String oldname, String newname){
        String sql = "UPDATE klas SET klasNaam ='" + newname
                + "' WHERE klasNaam='" + oldname
                + "' AND brinNummer='" + applicatieMain.getBrinNummer() + "'";
        database.executeUpdate(conn, sql);

        sql = "UPDATE userdata SET klas='" + newname
                + "' WHERE klas='" + oldname
                + "' AND brinNummer='" + applicatieMain.getBrinNummer() + "'";
        database.executeUpdate(conn, sql);
    }

    public void deleteLeerling(String leerlingNaam, int brinNummer, String klasNaam) {
        int userID = getUserID(leerlingNaam);
        String sql = "DELETE FROM userdata WHERE naam='" + leerlingNaam + "' AND brinNummer='"+brinNummer+"'";
        database.executeUpdate(conn, sql);

        sql = "DELETE FROM userlog WHERE userID='"+userID+"'";
        database.executeUpdate(conn, sql);
    }

    public void setStatus(int status) {

        /*
         * status: 0 = offline
         * status: 1 = online
         */
        String sql = "UPDATE userdata SET status ='" + status + "'"
                + " WHERE naam='" + applicatieMain.getUsername() + "'"
                + " AND brinNummer='" + applicatieMain.getBrinNummer() + "'";
        database.executeUpdate(conn, sql);
        System.out.println("STATUS SET: "+status);
    }

    public int getStatus(String naam) {
        /*
         * status: 0 = offline
         * status: 1 = online
         */


        String sql = "SELECT status FROM userdata"
                + " WHERE naam='" + naam + "'"
                + " AND brinNummer='" + applicatieMain.getBrinNummer() + "'";
        ResultSet result = database.execute(conn, sql);


        int status = -1;
        try {
            if (result.next()) {
                status = result.getInt("status");
                System.out.println("STATUS: "+status);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }

        return status;

    }
}
